package com.xxx.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

import com.xxx.common.dto.Pagination;
import com.xxx.common.utils.DateUtil;
import com.xxx.model.Dossier;

@Repository
public class DossierDao {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 保存
	 * @param dossier
	 * @return id
	 */
	public long save(Dossier dossier){
		String sql = "insert into t_dossier(id,username,idcard,birthdate,ismarry,phone,remark,recordstatus,createtime,updatetime) values (?,?,?,?,?,?,?,1,?,?)";
		Object obj[] = {dossier.getId(),dossier.getUsername(),dossier.getIdcard(),dossier.getBirthdate(),dossier.getIsmarry(),dossier.getPhone(),dossier.getRemark(),dossier.getCreatetime(),dossier.getUpdatetime()};
		int ret = jdbcTemplate.update(sql, obj);
		if (ret > 0) {
			return dossier.getId();
		}
		return 0;
	}
	
	/**
	 * 修改
	 * @param dossier
	 * @return id
	 */
	public long update(Dossier dossier){
		String sql = "update t_dossier set username=?,idcard=?,birthdate=?,ismarry=?,phone=?,remark=?,updatetime=? where id=?";
		Object obj[] = {dossier.getUsername(),dossier.getIdcard(),dossier.getBirthdate(),dossier.getIsmarry(),dossier.getPhone(),dossier.getRemark(),dossier.getUpdatetime(), dossier.getId()};
		int ret = jdbcTemplate.update(sql, obj);
		if (ret > 0) {
			return dossier.getId();
		}
		return 0;
	}
	
	/**
	 * 逻辑删除
	 * @param id
	 * @return true/false
	 */
	public boolean delete(long id){
		String sql = "update t_dossier set recordstatus=0,updatetime=? where id=?";
		Object obj[] = {DateUtil.dateToLong(),id};
		int ret = jdbcTemplate.update(sql, obj);
		return ret > 0;
	}
	
	/**
	 * 获取一个
	 * @param id
	 * @return Dossier
	 */	
	public Dossier getEntity(long id){
		String sql = "select id,username,idcard,birthdate,ismarry,phone,remark,createtime,updatetime from t_dossier where recordstatus=1 and id=?";
		final Dossier dossier = new Dossier();
		jdbcTemplate.query(sql, new Object[]{id},new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				getResultSet(rs, dossier);
            }
		});
		return dossier;
	}

	/**
	 * 获取list
	 * @param whereMap
	 * @return list
	 */		
	public List<Dossier> getList(Map<String, Object> whereMap){
		final List<Dossier> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select id,username,idcard,birthdate,ismarry,phone,remark,createtime,updatetime from t_dossier where recordstatus=1 ");
		List<Object> whereObj = new ArrayList<>();
		sql = getCondition(whereMap, sql, whereObj);
		Object obj[] = whereObj.toArray(new Object[whereObj.size()]);
		jdbcTemplate.query(sql.toString(), obj ,new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				list.add(getResultSet(rs, new Dossier()));
            }
		});
		return list;
	}

	/**
	 * 获取list,分页
	 * @param whereMap
	 * @param pagination
	 * @return list
	 */			
	public List<Dossier> getList(Map<String, Object> whereMap,Pagination pagination){
		final List<Dossier> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select id,username,idcard,birthdate,ismarry,phone,remark,createtime,updatetime from t_dossier where recordstatus=1 ");
		List<Object> whereObj = new ArrayList<>();
		sql = getCondition(whereMap, sql, whereObj);
		Object obj[] = whereObj.toArray(new Object[whereObj.size()]);
		sql.append(" order by " + pagination.getOrder());
		sql.append(" limit " + pagination.getPage() + "," + pagination.getPageSize());
		jdbcTemplate.query(sql.toString(), obj ,new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				list.add(getResultSet(rs, new Dossier()));
			}
		});
		return list;
	}

	/**
	 * 获取个数
	 * @param whereMap
	 * @return int
	 */		
	public int getCount(Map<String, Object> whereMap){
		StringBuffer sql = new StringBuffer("select count(1) from t_dossier where recordstatus=1");
		List<Object> whereObj = new ArrayList<>();
		sql = getCondition(whereMap, sql, whereObj);
		Object obj[] = whereObj.toArray(new Object[whereObj.size()]);
		Integer count = jdbcTemplate.queryForObject(sql.toString(), obj, Integer.class);
		if(count != null){
			return count;
		}
		return 0;
	}
	
	/**
	 * 获取条件
	 * @param whereMap
	 * @param sql
	 * @param whereObj
	 * @return sql
	 */	
	private StringBuffer getCondition(Map<String, Object> whereMap,StringBuffer sql,List<Object> whereObj){
		if (whereMap.containsKey("username")) {
			String username = (String) whereMap.get("username");
			sql.append(" and username like ?");
			whereObj.add(username + "%");
		}
		if (whereMap.containsKey("idcard")) {
			String idcard = (String) whereMap.get("idcard");
			sql.append(" and idcard=?");
			whereObj.add(idcard);
		}
		if (whereMap.containsKey("birthdate")) {
			int birthdate = (int) whereMap.get("birthdate");
			sql.append(" and birthdate=?");
			whereObj.add(birthdate);
		}
		if (whereMap.containsKey("ismarry")) {
			int ismarry = (int) whereMap.get("ismarry");
			sql.append(" and ismarry=?");
			whereObj.add(ismarry);
		}
		if (whereMap.containsKey("phone")) {
			String phone = (String) whereMap.get("phone");
			sql.append(" and phone=?");
			whereObj.add(phone);
		}
		if (whereMap.containsKey("remark")) {
			String remark = (String) whereMap.get("remark");
			sql.append(" and remark=?");
			whereObj.add(remark);
		}
		return sql;
	}
	
	/**
	 * 获取结果集
	 * @param whereMap
	 * @param dossier
	 * @return dossier
	 */	
	private Dossier getResultSet(ResultSet rs,Dossier dossier) throws SQLException{
		dossier.setId(rs.getLong("id"));
		dossier.setUsername(rs.getString("username"));
		dossier.setIdcard(rs.getString("idcard"));
		dossier.setBirthdate(rs.getString("birthdate"));
		dossier.setIsmarry(rs.getInt("ismarry"));
		dossier.setPhone(rs.getString("phone"));
		dossier.setRemark(rs.getString("remark"));
		dossier.setCreatetime(rs.getLong("createtime"));
		dossier.setUpdatetime(rs.getLong("updatetime"));
		return dossier;
	}
}